<?php
/**
 * I-Cafe framework
 * 
 * @category CF
 * @package CF_Db
 * @link http://www.i-cafe.org
 * @license http://www.i-cafe.org/license
 * @version 04-04-2010
 * @author tcl_java@yahoo.com
 */

/**
 * A class perfomance for build and execute query SELECT
 * <code>
 * 	$select = new CF_Db_Select();
 * 	$select->table(array("p" => "products"), array("name", "price"))
 * 		->where("`id` > %u", 2)
 * 		->order("price", "DESC");
 * 		->limit(10, 5);
 * 	echo $select; 
 * 	// output: "SELECT `name`, `price` FROM `products` AS `p` WHERE `id` > 2 ORDER BY `price` DESC LIMIT 10 OFFSET 5"
 * </code>
 */
class CF_Db_Select extends CF_Component implements CF_Db_Query_Interface
{
	/**
	 * @var boolean
	 */
	protected $_distinct;
	
	/**
	 * @var string
	 */
	protected $_foundRows;
	
	/**
	 * @var array
	 */
	protected $_table;
	
	/**
	 * @var array
	 */
	protected $_columns;
	
	/**
	 * @var array
	 */
	protected $_join;
	
	/**
	 * @var array
	 */
	protected $_where;
	
	/**
	 * @var array
	 */
	protected $_group;
	
	/**
	 * @var array
	 */
	protected $_having;
	
	/**
	 * @var array
	 */
	protected $_order;
	
	/**
	 * @var string
	 */
	protected $_limit;
	
	/**
	 * 
	 */
	public function __construct()
	{
		# notify before construct event
		$this->fireEvent('beforeconstruct', array($this));
		
		$this->addEvents(
			'beforebuildquery',
			'buildquery',
			'beforeexecute',
			'execute'
		);
		
		$this->reset();
		
		# notify construct event
		$this->fireEvent('construct', array($this));
	}
	
	/**
	 * 
	 */
	public function reset()
	{
		$this->_distinct = false;
		$this->_foundRows = false;
		$this->_table = array();
		$this->_columns = array();
		$this->_join = array();
		$this->_where = array('WHERE 1');
		$this->_group = array();
		$this->_having = array();
		$this->_order = array();
		$this->_limit = null;
	}
	
	/**
	 * 
	 * @return CF_Db_Select this
	 */
	public function distinct($distinct = true)
	{
		$this->_distinct = $distinct;
		return $this;
	}
	
	/**
	 * 
	 * @return CF_Db_Select this
	 */
	public function foundRows($foundRows = true)
	{
		$this->_foundRows = $foundRows;
		return $this;
	}
	
	/**
	 * 
	 * @param string|array|CF_Db_Expr $table
	 * @param string|array|CF_Db_Expr $columns [optional]
	 * @param string|array|CF_Db_Expr $where [optional]
	 * @return CF_Db_Select this
	 */
	public function table($table, $columns = '*', $where = null)
	{
		if(is_string($table) || $table instanceof CF_Db_Expr)
			$table = array($table);
			
		$this->_table = $table;
		
		reset($table);
		while($tb = current($table)){
			
			if(is_string($alias = key($table)))
				$this->_columns[$alias] = $columns;
				
			elseif(is_string($tb))
				$this->_columns[$tb] = $columns;
				
			elseif(is_array($tb))
				$this->_columns[implode('.', $tb)] = $columns;
				
			else
				$this->_columns[] = $columns;
			
			next($table);
		}
		#
		$this->where($where);
		
		return $this;
	}
	
	/**
	 * 
	 * @param string $type
	 * @param string|array|CF_Db_Expr $table
	 * @param string $cond
	 * @param array $columns [optional]
	 * @return CF_Db_Select this
	 */
	protected function _join($type, $table, $cond, array $columns = array())
	{
		$this->_join[] = array(
			'type' 		=> $type,
			'table' 	=> $table,
			'cond' 		=> $cond
		);
		
		if($columns){
			if(is_string($table)){
				$this->_columns[$table] = $columns;
				
			}elseif(is_array($table)){
				if(is_string($alias = key($table))){
					$this->_columns[$alias] = $columns;
					
				}else{
					$name = current($table);
					if(is_array($name)) $name = implode('.', $name);
					
					if(is_string($name)){
						$this->_columns[$name] = $columns;
					}
				}
			}
		}
		
		return $this;
	}
	
	/**
	 * 
	 * @param string|array|CF_Db_Expr $table
	 * @param string $cond
	 * @param array $columns [optional]
	 * @return CF_Db_Select this
	 */
	public function join($table, $cond, array $columns = array())
	{
		return $this->_join('', $table, $cond, $columns);
	}
	
	/**
	 * 
	 * @param string|array|CF_Db_Expr $table
	 * @param string $cond
	 * @param array $columns [optional]
	 * @return CF_Db_Select this
	 */
	public function joinInner($table, $cond, array $columns = array())
	{
		return $this->_join('INNER', $table, $cond, $columns);
	}
	
	/**
	 * 
	 * @param string|array|CF_Db_Expr $table
	 * @param string $cond
	 * @param array $columns [optional]
	 * @return CF_Db_Select this
	 */
	public function joinCross($table, $cond, array $columns = array())
	{
		return $this->_join('CROSS', $table, $cond, $columns);
	}
	
	/**
	 * 
	 * @param string|array|CF_Db_Expr $table
	 * @param string $cond
	 * @param array $columns [optional]
	 * @return CF_Db_Select this
	 */
	public function joinLeft($table, $cond, array $columns = array())
	{
		return $this->_join('LEFT', $table, $cond, $columns);
	}
	
	/**
	 * 
	 * @param string|array|CF_Db_Expr $table
	 * @param string $cond
	 * @param array $columns [optional]
	 * @return CF_Db_Select this
	 */
	public function joinRight($table, $cond, array $columns = array())
	{
		return $this->_join('RIGHT', $table, $cond, $columns);
	}
	
	/**
	 * 
	 * @param string|array|CF_Db_Expr $table
	 * @param string $cond
	 * @param array $columns [optional]
	 * @return CF_Db_Select this
	 */
	public function joinNatural($table, $cond, array $columns = array())
	{
		return $this->_join('NATURAL', $table, $cond, $columns);
	}
	
	/**
	 * 
	 * @param string $type
	 * @param string|array|CF_Db_Expr $table
	 * @param string|array|CF_Db_Expr $using
	 * @param array $columns [optional]
	 * @return CF_Db_Select this
	 */
	protected function _joinUsing($type, $table, $using, $columns = array())
	{
		$this->_join[] = array(
			'type' 		=> $type,
			'table' 	=> $table,
			'columns'	=> $using
		);
		
		if($columns){
			if(is_string($table)){
				$this->_columns[$table] = $columns;
				
			}elseif(is_array($table)){
				if(is_string($alias = key($table))){
					$this->_columns[$alias] = $columns;
					
				}else{
					$name = current($table);
					if(is_array($name)) $name = implode('.', $name);
					
					if(is_string($name)){
						$this->_columns[$name] = $columns;
					}
				}
			}
		}
		
		return $this;
	}
	
	/**
	 * 
	 * @param string|array|CF_Db_Exxpr $table
	 * @param string|array|CF_Db_Expr $using
	 * @param array $columns [optional]
	 * @return CF_Db_Select this
	 */
	public function joinUsing($table, $using, array $columns = array())
	{
		return $this->_joinUsing('', $table, $using, $columns);
	}
	
	/**
	 * 
	 * @param string|array|CF_Db_Exxpr $table
	 * @param string|array|CF_Db_Expr $using
	 * @param array $columns [optional]
	 * @return CF_Db_Select this
	 */
	public function joinInnerUsing($table, $using, array $columns = array())
	{
		return $this->_joinUsing('INNER', $table, $using, $columns);
	}
	
	/**
	 * 
	 * @param string|array|CF_Db_Exxpr $table
	 * @param string|array|CF_Db_Expr $using
	 * @param array $columns [optional]
	 * @return CF_Db_Select this
	 */
	public function joinCrossUsing($table, $using, array $columns = array())
	{
		return $this->_joinUsing('CROSS', $table, $using, $columns);
	}
	
	/**
	 * 
	 * @param string|array|CF_Db_Exxpr $table
	 * @param string|array|CF_Db_Expr $using
	 * @param array $columns [optional]
	 * @return CF_Db_Select this
	 */
	public function joinLeftUsing($table, $using, array $columns = array())
	{
		return $this->_joinUsing('LEFT', $table, $using, $columns);
	}
	
	/**
	 * 
	 * @param string|array|CF_Db_Exxpr $table
	 * @param string|array|CF_Db_Expr $using
	 * @param array $columns [optional]
	 * @return CF_Db_Select this
	 */
	public function joinRightUsing($table, $using, array $columns = array())
	{
		return $this->_joinUsing('RIGHT', $table, $using, $columns);
	}
	
	/**
	 * 
	 * @param string|array|CF_Db_Exxpr $table
	 * @param string|array|CF_Db_Expr $using
	 * @param array $columns [optional]
	 * @return CF_Db_Select this
	 */
	public function joinNaturalUsing($table, $using, array $columns = array())
	{
		return $this->_joinUsing('NATURAL', $table, $using, $columns);
	}
	
	/**
	 * 
	 * @param string|CF_Db_Expr $cond
	 * @param mixed $values [optional]
	 * @return CF_Db_Update this
	 */
	public function where($cond, $values = null)
	{
		$this->_where[] = CF_Db_QueryPart::where($cond, $values);
		return $this;
	}
	
	/**
	 * 
	 * @param string|CF_Db_Expr $cond
	 * @param mixed $values [optional]
	 * @return 
	 */
	public function orWhere($cond, $values = null)
	{
		$this->_where[] = CF_Db_QueryPart::orWhere($cond, $values);
		return $this;
	}
	
	/**
	 * 
	 * @param string|CF_Db_Expr $cond
	 * @param mixed $values [optional]
	 * @return CF_Db_Update this
	 */
	public function having($cond, $values = null)
	{
		$this->_having[] = CF_Db_QueryPart::where($cond, $values);
		return $this;
	}
	
	/**
	 * 
	 * @param string|CF_Db_Expr $cond
	 * @param mixed $values [optional]
	 * @return 
	 */
	public function orHaving($cond, $values = null)
	{
		$this->_having[] = CF_Db_QueryPart::orWhere($cond, $values);
		return $this;
	}
	
	/**
	 * 
	 * @param string|array|CF_Db_Expr $column
	 * @param string $direction [optional]
	 * @return CF_Db_Select this
	 */
	public function group($column, $direction = null)
	{
		if($column instanceof CF_Db_Expr){
			$this->_group[] = (string) $column;
		}else{
			$g = CF_Db::getInstance()->quoteIdentifier($column);
			if($direction) $g .= " $direction";
			$this->_group[] = $g;
		}
		
		return $this;
	}
	
	/**
	 * 
	 * @param string|array $columns
	 * @param string $direction [optional]
	 * @return CF_Db_Update this
	 */
	public function order($columns, $direction = null)
	{
		$order = CF_Db_QueryPart::order($columns, $direction);
		if($order) $this->_order[] = $order;
		return $this;
	}
	
	/**
	 * 
	 * @param int $count
	 * @param int $offset [optional]
	 * @return CF_Db_Update this
	 */
	public function limit($count, $offset = null)
	{
		$this->_limit = CF_Db_QueryPart::limit($count, $offset);
		return $this;
	}
	
	/**
	 * 
	 * @return CF_Db_Store
	 */
	public function execute()
	{
		$query = $this->__toString();
		
		# notify before execute event
		if(CF_Observable::VALUE_RETURN === $this->fireEvent('beforeexecute', array($this, &$query))) return;
		
		$res = new CF_Db_Store($query);
		
		# notify execute event
		$this->fireEvent('execute', array($this, $res, &$query));
		
		return $res;
	}
	
	/**
	 * 
	 * @return string
	 */
	public function __toString()
	{
		# notify before build query event
		if(CF_Observable::VALUE_RETURN === $this->fireEvent('beforebuildquery', array($this))) return;
		
		$parts = array();
		$parts[] = 'SELECT';
		
		# distinct ?
		if($this->_distinct)
			$parts[] = 'DISTINCT';
			
		# found rows ?
		if($this->_foundRows)
			$parts[] = 'SQL_CALC_FOUND_ROWS';
		
		# render columns
		$cols = array();
		$_columns = $this->_columns;
		foreach($_columns as $tbAlias => $columns){
			if(is_string($tbAlias))
				$tb = CF_Db::getInstance()->quoteIdentifier($tbAlias) . '.';
			else
				$tb = '';
				
			$col = array();
			if($columns instanceof CF_Db_Expr){
				$cols[] = (string) $columns;
			}elseif('*' == $columns){
				$cols[] = $tb . '*';
			}else{
				if(is_string($columns)){
					$columns = preg_replace('/\s+/', '', $columns);
					$columns = explode(',', $columns);
				}
				foreach($columns as $alias => $column){
					$c = $tb . CF_Db::getInstance()->quoteIdentifier($column);
					if(is_string($alias))
						$c .= ' AS ' . CF_Db::getInstance()->quoteIdentifier($alias);
					$col[] = $c;
				}
				$cols[] = implode(',', $col);
			}
		}
		$parts[] = implode(',', $cols);
		
		# render table
		$parts[] = 'FROM';
		$alias = array_keys($this->_table);
		$names = array_values($this->_table);
		
		foreach($alias as &$a)
			$a = CF_Db::getInstance()->quoteIdentifier($a);
			
		foreach($names as &$n)
			$n = CF_Db::getInstance()->quoteIdentifier($n);
		
		$_table = $this->_table;
		$_table = array_combine($alias, $names);
		
		$tb = array();
		foreach($_table as $alias => $name){
			$tb[] = $name . (is_string($alias)? " AS $alias" : '');	
		}
		
		$parts[] = implode(',', $tb);
		
		# render join
		$_join = $this->_join;
		foreach($_join as $join){
			$j = $join['type']? $join['type'] . ' JOIN ' : 'JOIN ';
			
			if($join['table'] instanceof CF_Db_Expr){
				$j .= $join['table'];
			}elseif(is_string($join['table'])){
				$j .= CF_Db::getInstance()->quoteIdentifier($join['table']);
			}else{
				$j .= CF_Db::getInstance()->quoteIdentifier(current($join['table']));
				if(is_string($alias = key($join['table']))){
					$j .= ' AS ' . CF_Db::getInstance()->quoteIdentifier($alias);
				}
			}
			
			if(isset($join['columns']) && $join['columns']){
				$j .= ' USING ';
				if($join['columns'] instanceof CF_Db_Expr){
					$cond = (string) $join['columns'];
				}else{
					if(is_string($join['columns'])){
						$join['columns'] = preg_replace('/\s+/', '', $join['columns']);
						$join['columns'] = explode(',', $join['columns']);
					}
					foreach($join['columns'] as &$col){
						$col = CF_Db::getInstance()->quoteIdentifier($col);
					}
					$cond = '(' . implode(',', $join['columns']) . ')';
				}
			}else{
				$j .= ' ON ';
				$cond = $join['cond'];
			}
			$j .= $cond;
			$parts[] = $j;
		}
		
		# render where
		if($this->_where){
			$parts[] = implode(' ', $this->_where);
		}
		
		# render group
		if($this->_group){
			$parts[] = 'GROUP BY ' . implode(',', $this->_group);
		}
		
		# render having
		if($this->_having){
			$parts[] = 'HAVING 1 ' . implode(' ', $this->_where);
		}
		
		# render order
		if($this->_order){
			$parts[] = 'ORDER BY ' . implode(',', $this->_order);
		}
		
		# render limit
		if($this->_limit){
			$parts[] = $this->_limit;
		}
		
		$query = implode(' ', $parts);
		
		# notify build query event
		$this->fireEvent('buildquery', array($this, &$query));
		
		return $query;
	}
}
